package c4.billing.gui;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ListIterator;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ViewEditBill extends HttpServlet{
	 
	public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
		
		Statement stmt = null;
		ResultSet rs = null;
		String query;
		
		String accountTable = "account_information";
		String custName = "name";
		String primaryPhoneColumn = "account_number";
		String custStreet = "street";
		String custCity = "city";
		String custState = "state";
		String custZIP = "zip";
		String billingDay = "billing_start_day";
		String phoneNumber = "phone_number";
		String phoneNumberListing = "phone_number_listing";
		String duration = "duration";
		String callLog = "call_log";
		String dollars = "dollars";
		String serviceName = "service_name";
		String rates = "rates";
		String voicemail = "voicemail";
		String callerID = "caller_id";
		String threeWay = "three_way";
		String charges = "charges";
		String taxes1 = "taxes";
		String total1 = "total";
		String revenueReporting = "revunue_reporting";
		String cyclePeriod = "cycle_period";
		String otherNumbers = "other_number";
		String dateMade = "date_made";
		
		Vector token = new Vector();
		ListIterator iter = token.listIterator();
		Vector token1 = new Vector();
		ListIterator iter1 = token1.listIterator();
		Vector token2 = new Vector();
		ListIterator iter2 = token2.listIterator();
		Vector token3 = new Vector();
		ListIterator iter3 = token3.listIterator();
		Vector token4 = new Vector();
		ListIterator iter4 = token4.listIterator();
		
		int accountNumber = Integer.parseInt(request.getParameter("customerID"));
		
		try{
			Connection con = connection();
			
			stmt = con.createStatement();
			query = "SELECT" + custName + "FROM" + accountTable + "WHERE" + primaryPhoneColumn + "=" + accountNumber;
			
			rs = stmt.executeQuery(query);
			String customerName = rs.getString(custName);
			
			stmt = con.createStatement();
			query = "SELECT" + custStreet + "FROM" + accountTable + "WHERE" + primaryPhoneColumn + "=" + accountNumber;
			
			rs = stmt.executeQuery(query);
			String customerStreet = rs.getString(custStreet);
			
			stmt = con.createStatement();
			query = "SELECT" + custCity + "FROM" + accountTable + "WHERE" + primaryPhoneColumn + "=" + accountNumber;
			
			rs = stmt.executeQuery(query);
			String customerCity = rs.getString(custCity);
			
			stmt = con.createStatement();
			query = "SELECT" + custState + "FROM" + accountTable + "WHERE" + primaryPhoneColumn + "=" + accountNumber;
			
			rs = stmt.executeQuery(query);
			String customerState = rs.getString(custState);
			
			stmt = con.createStatement();
			query = "SELECT" + custZIP + "FROM" + accountTable + "WHERE" + primaryPhoneColumn + "=" + accountNumber;
			
			rs = stmt.executeQuery(query);
			String customerZIP = rs.getString(custZIP);
			
			String address = customerStreet + "," + customerCity + "," + customerState + "," + customerZIP;
			
			query = "SELECT "+ phoneNumber +" FROM " + phoneNumberListing+ " WHERE "+ primaryPhoneColumn + " = " + accountNumber;
	    	rs = stmt.executeQuery(query);
		    
	    	//store the phone numbers on each account in token Vector
		    while(rs.next()) {
		    	 token.add(rs.getString(phoneNumber));
		    }
		    
		    String phoneNumbers = null;
		    while(iter.hasNext()){
		    	phoneNumbers = phoneNumbers +  "," + (String)iter.next();
		    }
		    
		    stmt = con.createStatement();
			query = "SELECT" + billingDay + "FROM" + accountTable + "WHERE" + primaryPhoneColumn + "=" + accountNumber;
			
			rs = stmt.executeQuery(query);
			String billDay = rs.getString(billingDay);
		    
		    stmt = con.createStatement();
			query = "SELECT" + cyclePeriod + "FROM" + revenueReporting + "WHERE" + billingDay + "=" + billDay;
			
			rs = stmt.executeQuery(query);
			String billingCycle = rs.getString(cyclePeriod);
			
			query = "SELECT "+ phoneNumber +" FROM " + phoneNumberListing+ " WHERE "+ primaryPhoneColumn + " = " + accountNumber;
	    	rs = stmt.executeQuery(query);
		    
	    	//store the phone numbers on each account in token Vector
		    while(rs.next()) {
		    	 token.add(rs.getString(phoneNumber));
		    }
		    
		    while(iter.hasNext()){
		    	query = "SELECT "+ otherNumbers +" FROM " + callLog + " WHERE "+ phoneNumber + " = " + (String)iter.next();
		    	rs = stmt.executeQuery(query);
		    	
		    	while(rs.next()) {
			    	 token1.add(rs.getString(otherNumbers));
			    }
		    	
		    	query = "SELECT "+ dateMade +" FROM " + callLog + " WHERE "+ phoneNumber + " = " + (String)iter.next();
		    	rs = stmt.executeQuery(query);
		    	
		    	while(rs.next()) {
			    	 token2.add(rs.getDate(dateMade));
			    }
		    	
		    	query = "SELECT "+ duration +" FROM " + callLog + " WHERE "+ phoneNumber + " = " + (String)iter.next();
		    	rs = stmt.executeQuery(query);
		    	
		    	while(rs.next()) {
			    	 token3.add(rs.getInt(duration));
			    }
		    	
				query = "SELECT" + total1 + "FROM" + charges + "WHERE" + phoneNumber + " = " + (String)iter.next();
				rs = stmt.executeQuery(query);
				
				while(rs.next()){
					token4.add(rs.getDouble(total1));
				}
				
				double billAmount = 0;
			    while(iter4.hasNext()){
			    	double tot = Double.parseDouble((String)iter4.next());
			    	billAmount = billAmount + tot;
			    }
		    }
		    	
		}
		catch(Exception e){
			
		}
		
		
		PrintWriter out = response.getWriter();
		out.println("<html>");
		out.println("<head>");
		out.println("<title> Customer Bill </title>");
		out.println("</head>");
		out.println("<body bgcolor=\"#b4b4ef\">");
		out.println("<form action=\"SaveBill.java\" name=\"viewBill\" method=\"post\">");
		out.println("<table align=\"center\" border=\"0\" width=\"0%\">");
		out.println("<tr><td align=\"center\"><h1>Customer Bill</h1></td></tr>");
		out.println("</table>");
		out.println("<table align=\"center\" border=\"0\" width=\"100%\">");
		out.println("<tr>");
		out.println("<td align=\"right\" width=\"150\"><label for=\"customerName\"><b>CUSTOMER NAME:</b></label></td>");
		out.println("<td width=\"10\"><spacer type=\"horizontal\" width=\"10\"></td>");
		out.println("<td align=\"left\" width=\"0%\"><input type=\"text\" id=\"customerID\" name=\"pin\" value=\"\" size=\"30\" maxlength=\"60\"></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<table align=\"center\" border=\"0\" width=\"100%\">");
		out.println("<tr>");
		out.println("<td align=\"right\" width=\"150\"><label for=\"address\"><b>ADDRESS:</b></label></td>");
		out.println("<td width=\"10\"><spacer type=\"horizontal\" width=\"10\"></td>");
		out.println("<td><textarea name=\"content\" cols=\"50\" rows=\"5\"></textarea></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<table align=\"center\" border=\"0\" width=\"100%\">");
		out.println("<tr>");
		out.println("<td align=\"right\" width=\"150\"><label for=\"phoneNumber\"><b>PHONE NUMBER:</b></label></td>");
		out.println("<td width=\"10\"><spacer type=\"horizontal\" width=\"10\"></td>");
		out.println("<td align=\"left\" width=\"0%\"><input type=\"text\" id=\"phoneNo\" name=\"phoneNo\" value=\"\" size=\"30\" maxlength=\"60\"></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<br></br>");
		out.println("<table align=\"center\" border=\"0\" width=\"100%\">");
		out.println("<tr>");
		out.println("<td align=\"right\" width=\"150\"><label for=\"billingCycle\"><b>BILLING CYCLE:</b></label></td>");
		out.println("<td width=\"10\"><spacer type=\"horizontal\" width=\"10\"></td>");
		out.println("<td align=\"left\" width=\"0%\"><input type=\"text\" id=\"billingClc\" name=\"billingClc\" value=\"\" size=\"30\" maxlength=\"60\"></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<br></br>");
		out.println("<table align=\"left\" border=\"5\" width=\"50%\">");
		out.println("<tr>");
		out.println("<td align=\"middle\" width=\"25\"\"><label for=\"numbersCR\"><b>Number Called/Received</b></label></td>");
		out.println("<td align=\"middle\" width=\"20\"><label for=\"date\"><b>Date</b></label></td>");
		out.println("<td align=\"middle\" width=\"10\"><label for=\"min\"><b>Minutes</b></label></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<br></br>");
		out.println("<table align=\"center\" border=\"0\" width=\"100%\">");
		out.println("<tr>");
		out.println("<td align=\"right\" width=\"150\"><label for=\"servicesUsed\"><b>SERVICES USED:</b></label></td>");
		out.println("<td width=\"10\"><spacer type=\"horizontal\" width=\"10\"></td>");
		out.println("<td><textarea name=\"content\" cols=\"10\" rows=\"3\"></textarea></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<br></br>");
		out.println("<table align=\"center\" border=\"0\" width=\"100%\">");
		out.println("<tr>");
		out.println("<td align=\"right\" width=\"150\"><label for=\"rates\"><b>RATES:</b></label></td>");
		out.println("<td width=\"10\"><spacer type=\"horizontal\" width=\"10\"></td>");
		out.println("<td align=\"left\"> Call - $0.10/minute, VoiceMail - $5.00/month, Three-Way - $2.5/month, CallerID - $1.75/month, Taxes - $0.02(on Total Charges)</td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<table align=\"center\" border=\"0\" width=\"100%\">");
		out.println("<tr>");
		out.println("<td align=\"right\" width=\"150\"><label for=\"billAmount\"><b>BILL AMOUNT:</b></label></td>");
		out.println("<td width=\"10\"><spacer type=\"horizontal\" width=\"10\"></td>");
		out.println("<td align=\"left\" width=\"0%\"><input type=\"text\" id=\"billAMT\" name=\"billAMT\" value=\"\" size=\"30\" maxlength=\"60\"></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<br></br>");
		out.println("<table align = \"center\" border=\"0\" width=\"50%\">");
		out.println("<tr>");
		out.println("<td></td>");
		out.println("<td align = \"center\"><input type=\"submit\" id=\"SaveChange\" name=\"SaveChange\" value=\"Save Changes\"/></td>");
		out.println("<td align = \"center\"><input type=\"submit\" id=\"exit\" name=\"exit\" value=\"Exit\"/></td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("</body>");
		out.println("</html>");

	}
	
	public Connection connection() throws InstantiationException, IllegalAccessException, ClassNotFoundException{
		
		Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    conn = DriverManager.getConnection("jdbc:mysql://localhost/corporate?" + 
		                                   "user=c4_user&password=p@ssw0rd");
		}
		catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		return conn;
	}
}
